package com.huayi.website.repository;

import com.huayi.website.entity.MessageBoardEntity;
import com.huayi.website.entity.NewsEntity;
import com.huayi.website.utils.PagingResponse;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

@Repository
public class MessageBoardRepository {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     *
     * @param pageIndex
     * @param pageSize
     * @param linkMan
     * @param phone
     * @param email
     * @return
     */
    @Transactional
    public PagingResponse<MessageBoardEntity> getList(int pageIndex, int pageSize, String linkMan, String phone, String email){
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT SQL_CALC_FOUND_ROWS * FROM").append(Tables.MessageBoard);

        sb.append(" where 1=1 ");
        List<Object> params = new ArrayList<>();

        if (!StringUtils.isEmpty(linkMan)) {
            sb.append(" AND linkMan LIKE ? ");
            params.add("%" + linkMan + "%");
        }
        if (!StringUtils.isEmpty(phone)) {
            sb.append(" AND linkPhone = ? ");
            params.add( phone );
        }
        if (!StringUtils.isEmpty(email)) {
            sb.append(" AND email = ? ");
            params.add( email );
        }

        sb.append(" ORDER BY id DESC LIMIT ?,?");
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);

        var list = jdbcTemplate.query(sb.toString(),new BeanPropertyRowMapper<>(MessageBoardEntity.class),params.toArray(new Object[params.size()]));

        int total = jdbcTemplate.queryForObject("SELECT FOUND_ROWS() as row_num;", int.class);

        return new PagingResponse<>(pageIndex, pageSize, total, list);
    }


    /**
     * 添加
     * @param linkName
     * @param linkPhone
     * @param email
     * @param content
     * @return
     */
     public  Integer addMessage(String linkName,String linkPhone,String email,String content){
        String sql = "INSERT INTO "+Tables.MessageBoard+" (linkMan,linkPhone,email,content,status,createTime) VALUE (?,?,?,?,?,?)";
        KeyHolder keyHolder = new GeneratedKeyHolder();

        jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                ps.setString(1, linkName);
                ps.setString(2, linkPhone);
                ps.setString(3, email);
                ps.setString(4, content);
                ps.setInt(5, 0);
                ps.setLong(6, System.currentTimeMillis() / 1000);

                return ps;
            }
        }, keyHolder);

        Integer id = keyHolder.getKey().intValue();
        return id;
    }

    public void messageReply(Integer messageId,String userName, String remark){
        jdbcTemplate.update("UPDATE "+Tables.MessageBoard+" SET status=1,replyMan=?,replyTime=?,replyRemark=? WHERE id=?",
                userName,System.currentTimeMillis() / 1000,remark,messageId);
    }
}
